import pandas as pd
df_xz = pd.read_excel(
    r'http://72.itmc.org.cn:80/JS001/data/user/15108/242/fj_employee_salary_work_books.xlsx', sheet_name='基本薪资')
df_tq = pd.read_excel(
    r'http://72.itmc.org.cn:80/JS001/data/user/15108/242/fj_employee_salary_work_books.xlsx', sheet_name='上班通勤')
df_total = pd.merge(df_xz, df_tq, left_on='姓名', right_on='姓名')
df_total['应发工资'] = df_total['基本薪资'] + \
                   df_total['岗位工资'] + df_total['绩效工资']
df_total.fillna(0, inplace=True)
df_total['加班工资'] = df_total['应发工资'] / df_total['应出勤天数（天）'] / 8 * (
        df_total['工作日加班（小时）'] + df_total['周末加班（小时）'] * 1.5 + df_total['法定假日加班（小时）'] * 2 - df_total['请假（小时）'])
df_total['五险一金'] = df_total['社会保险缴费基数'] * (0.08 + 0.02 + 0.01 + 0.1)
df_total['实发工资'] = df_total['应发工资'] + \
                   df_total['加班工资'] - df_total['五险一金']
df_total.to_excel('res2.xlsx')
df_ret = df_total[['部门', '实发工资']].groupby('部门').mean().round(2)
ret_se = df_ret.sort_values(by='实发工资', ascending=False)['实发工资']
ret_se.name = '平均薪资'
print(ret_se)